package com.example.demozy.mapper;

import com.example.demozy.entity.Condition;
import com.example.demozy.entity.ConditionSheet;
import com.example.demozy.entity.SheetCondition;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

/**
 * @author 张展鹏
 */
@Mapper
public interface ConditionSheetMapper {

    /**
     * 获取用户所有的条件单
     *
     * @param userId : 用户ID
     */
    @Select("select * " +
            "from condition_sheet " +
            "where author_id = #{userId}")
    List<ConditionSheet> listAllConditionSheets(@Param("userId") String userId);

    /**
     * 获取用户所有的条件单，从订单库
     *
     * @param userId : 用户ID
     */
    @Select("SELECT condition_sheet.* FROM condition_sheet, `order`,condition_sheet_commodity " +
            "WHERE `order`.user_id = #{userId} AND `order`.commodity_id = condition_sheet_commodity.commodity_id " +
            " AND condition_sheet_commodity.condition_sheet_id = condition_sheet.condition_sheet_id")
    List<ConditionSheet> listAllOrderConditionSheets(@Param("userId") String userId);

    /**
     * 获得指定未上架的条件单的所有条件及其参数
     *
     * @param conditionSheetId : 条件单ID
     */
    @Select("select `condition`.*, `sheet-condition`.condition_parameter, `sheet-condition`.order_type " +
            "from condition_sheet, `sheet-condition`, `condition` " +
            "where condition_sheet.condition_sheet_id = #{conditionSheetId} AND `sheet-condition`.condition_sheet_id = #{conditionSheetId} AND `condition`.condition_id = `sheet-condition`.condition_id")
    List<SheetCondition> listConditionSheetsConditions(@Param("conditionSheetId") String conditionSheetId);

    /**
     * 获取condition_sheet表最新的condition_sheet_id
     */
    @Select("select max(cast(condition_sheet_id as SIGNED)) " + "from condition_sheet")
    int getMaxConditionSheetId();

    /**
     * 插入条件单表，仅条件单
     *
     * @param conditionSheetId : 条件单ID
     * @param conditionSheetName : 条件单名称
     * @param authorId : 作者ID
     */
    @Insert("insert into condition_sheet " + "values(#{conditionSheetId},#{conditionSheetName},0,#{authorId}," +
            "now(),now())")
    int insertConditionSheet(@Param("conditionSheetId") String conditionSheetId,
            @Param("conditionSheetName") String conditionSheetName, @Param("authorId") String authorId);

    /**
     * 插入条件单与条件的关联表。
     *
     * @param conditionSheetId : 条件单ID
     * @param conditionId : 条件ID
     * @param conditionParameter : 条件参数
     * @param orderType : 买卖类型，0是买，1是卖。
     */
    @Insert("insert into `sheet-condition` " + "values(#{conditionSheetId},#{conditionId},#{conditionParameter}," +
            "#{orderType})")
    int insertSheetCondition(@Param("conditionSheetId") String conditionSheetId,
            @Param("conditionId") String conditionId, @Param("conditionParameter") String conditionParameter
            , @Param("orderType") int orderType);

    /**
     * 获取数据库中所有条件。
     */
    @Select("select * from `condition`")
    List<Condition> listAllConditions();

    /**
     * 删除指定条件单
     *
     * @param conditionSheetId : 条件单ID
     */
    @Delete("delete from condition_sheet where condition_sheet_id = #{conditionSheetId}")
    int deleteConditionSheet(@Param("conditionSheetId") String conditionSheetId);

    /**
     * 删除指定条件单与条件对应的关联表数据
     *
     * @param conditionSheetId : 条件单ID
     */
    @Delete("delete from `sheet-condition` where condition_sheet_id = #{conditionSheetId}")
    int deleteSheetCondition(@Param("conditionSheetId") String conditionSheetId);

    /**
     * 更新指定条件单的名称
     *
     * @param conditionSheetId : 条件单ID
     * @param conditionSheetName : 条件单名称
     */
    @Update("update condition_sheet set condition_sheet_name = #{conditionSheetName}, update_time = now() " +
            "where condition_sheet_id = #{conditionSheetId}")
    int updateConditionSheetName(@Param("conditionSheetId") String conditionSheetId, @Param("conditionSheetName") String conditionSheetName);

    /**
     * 更新指定条件单的条件
     *
     * @param conditionSheetId : 条件单ID
     * @param conditionId : 条件ID
     * @param conditionParameter : 条件参数
     * @param orderType : 买卖类型，0是买，1是卖。
     */
    @Update("update `sheet-condition` set condition_parameter = #{conditionParameter}, order_type = #{orderType} " +
            "where condition_sheet_id = #{conditionSheetId} and condition_id = #{conditionId}")
    int updateCondition(@Param("conditionSheetId") String conditionSheetId, @Param("conditionId") String conditionId,
            @Param("conditionParameter") String conditionParameter, @Param("orderType") int orderType);

    /**
     * 更新指定条件单的上架情况
     *
     * @param conditionSheetId : 条件单ID
     * @param onState : 上架情况，0是未上架，1是已上架，2是待审核，3是已购买
     */
    @Update("update condition_sheet set on_state = #{onState} " +
            "where condition_sheet_id = #{conditionSheetId}")
    int updateConditionSheetOnState(@Param("conditionSheetId") String conditionSheetId, @Param("onState") int onState);

    /**
     * 获取back_test表最新的back_test_report_id
     */
    @Select("select max(cast(back_test_report_id as SIGNED)) " + "from back_test")
    int getMaxReportId();

    /**
     * 插入回测报告表。
     *
     * @param backTestReportId : 条回测报告ID
     * @param conditionSheetId : 条件单ID
     * @param backTestStock : 回测股票
     * @param startTime : 开始时间
     * @param endTime : 结束时间
     * @param yield : 收益率
     */
    @Insert("insert into `back_test` " + "values(#{backTestReportId},#{conditionSheetId},now(),#{backTestStock}," +
            "#{startTime},#{endTime},#{yield},now())")
    int saveBackTestReport(@Param("backTestReportId") String backTestReportId,
            @Param("conditionSheetId") String conditionSheetId, @Param("backTestStock") String backTestStock
            , @Param("startTime") Date startTime, @Param("endTime") Date endTime, @Param("yield") double yield);

    /**
     * 获取某指定条件单的上架状态
     *
     * @param conditionSheetId : 条件单ID
     */
    @Select("select on_state " + "from condition_sheet where condition_sheet_id = #{conditionSheetId}")
    int getConditionSheetOnState(@Param("conditionSheetId") String conditionSheetId);

    /**
     * 从订单库中获取用户购买的条件单。
     *
     * @param userId : 用户ID
     */
    @Select("SELECT condition_sheet_id, commodity_name FROM condition_sheet_commodity, `order` WHERE user_id = #{userId} " +
            "AND condition_sheet_commodity.commodity_id = `order`.commodity_id")
    List<ConditionSheet> listAllBuyConditionSheets(@Param("userId") String userId);

}
